﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;

namespace CodeGenerator
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void __selectDatabaseButton_Click(object sender, EventArgs e)
        {
            if (__openFileDialog.ShowDialog(this) != DialogResult.OK) return;


            __databaseFileTextBox.Text = __openFileDialog.FileName;
        }
        private readonly SqlCeConnection _cnn = new SqlCeConnection();
        private readonly StringBuilder _sb = new StringBuilder();
        private void __generateCodeButton_Click(object sender, EventArgs e)
        {
            _sb.Length = 0;

            AddText(@"using System;
using System.Data.SqlServerCe;
using System.Linq;
using Kea;

namespace MyProject
{
    internal class MyTableReaderWriter<T> : TableReaderWriter<T> where T : TableReaderWriter<T>, new()
    {
        private static SqlCeConnection _cnn;
        protected override SqlCeConnection ConnectionGet()
        {
            return _cnn ?? (_cnn = CreateConnection());
        }

        private static SqlCeConnection CreateConnection()
        {
            string cs = ""data source='");

            AddText(__databaseFileTextBox.Text.Replace("\\", "\\\\"));
            AddText(@"'"";
            var cnn = new SqlCeConnection(cs);
            cnn.Open();
            return cnn;
        }
    }
");

            _cnn.ConnectionString = "data source='" + __databaseFileTextBox.Text + "'";
            using (_cnn)
            {
                _cnn.Open();

                using (SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES", _cnn))
                using (var dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        AddTable((string)dr[2]);

                    }
                }
            }
            AddText("\r\n}");

            __codeTextBox.Text = _sb.ToString();
            _sb.Length = 0;
        }

        private void AddTable(string tn)
        {
            string className = tn.Replace(" ", "");
            AddText("internal class " + className + " : MyTableReaderWriter<" + className + @">
    {
");
            AddIndexes(tn);
            AddColumns(tn);
            AddText("}\r\n\r\n");
        }

        private class IndexCol
        {
            public bool PRIMARY_KEY { set; get; }
            public string INDEX_NAME { set; get; }
            public bool UNIQUE { set; get; }
            public int ORDINAL_POSITION { set; get; }
            public string COLUMN_NAME { set; get; }
            public bool IS_NULLABLE { set; get; }
            public string DATA_TYPE { set; get; }

        }
        private void AddIndexes(string tn)
        {

            var indexes = GetIndexes(tn).GroupBy(ix => ix.INDEX_NAME);

            foreach (var inx in indexes)
            {
                string methodName = "ReadBy" + inx.Key.Replace(" ", "");
                AddText("public static DataPlanIndexable<");
                AddText(tn.Replace(" ", ""));
                AddText("> ");
                AddText(methodName);
                AddText("(){return Read().WithIndex(\"");
                AddText(inx.Key);
                AddText("\");}\r\n");

                foreach (var c in Enumerable.Range(1, inx.Count()))
                {
                    AddText("public static DataPlanIndexable<");
                    AddText(tn.Replace(" ", ""));
                    AddText("> ReadBy");
                    AddText(inx.Key.Replace(" ", ""));
                    AddText("(");
                    bool comma = false;
                    foreach (var col in inx.OrderBy(ix => ix.ORDINAL_POSITION).Take(c))
                    {
                        if (comma) AddText(", "); else comma = true;
                        AddText(GetDotNetType(col.DATA_TYPE, col.IS_NULLABLE));
                        AddText(" ");
                        AddText(col.COLUMN_NAME.Replace(" ", ""));
                    }

                    AddText("){");
                    AddText("return "+methodName+"().IndexMatches(");
                    comma = false;
                    foreach (var col in inx.OrderBy(ix => ix.ORDINAL_POSITION).Take(c))
                    {
                        if (comma) AddText(", "); else comma = true;
                        AddText(col.COLUMN_NAME.Replace(" ", ""));
                    }
                    AddText(");");
                    AddText("}\r\n");
                }

            }
        }
        private IEnumerable<IndexCol> GetIndexes(string tn)
        {
            using (SqlCeCommand cmd = new SqlCeCommand(@"
SELECT IXS.PRIMARY_KEY, IXS.INDEX_NAME, IXS.[UNIQUE], IXS.ORDINAL_POSITION, IXS.COLUMN_NAME, CLS.IS_NULLABLE, CLS.DATA_TYPE
FROM        INFORMATION_SCHEMA.INDEXES IXS
INNER JOIN  INFORMATION_SCHEMA.COLUMNS CLS ON IXS.TABLE_NAME = CLS.TABLE_NAME AND IXS.COLUMN_NAME = CLS.COLUMN_NAME    
WHERE   IXS.TABLE_NAME = '" + tn + "'", _cnn))
            using (var dr = cmd.ExecuteReader())
                while (dr.Read())
                    yield return new IndexCol()
                    {
                        COLUMN_NAME = (string)dr["COLUMN_NAME"],
                        DATA_TYPE = (string)dr["DATA_TYPE"],
                        INDEX_NAME = (string)dr["INDEX_NAME"],
                        IS_NULLABLE = !"NO".Equals(dr["IS_NULLABLE"]),
                        ORDINAL_POSITION = (int)dr["ORDINAL_POSITION"],
                        PRIMARY_KEY = (bool)dr["PRIMARY_KEY"],
                        UNIQUE = (bool)dr["UNIQUE"]
                    };
        }
        private void AddColumns(string tn)
        {
            using (SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tn + "'", _cnn))
            using (var dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {


                    //for (int i = 0; i < dr.FieldCount; i++)
                    //{
                    //    AddText(dr.GetName(i));
                    //    AddText(":");
                    //    AddText(dr[i].ToString());
                    //    AddText("\r\n");
                    //}



                    string cn = (string)dr["COLUMN_NAME"];
                    string dt = (string)dr["DATA_TYPE"];
                    bool isNullable = "YES".Equals(dr["IS_NULLABLE"]);

                    AddText("public " + GetDotNetType(dt, isNullable) + " ");

                    AddText(cn.Replace(" ", ""));
                    AddText(" { get { return Get" + GetMethodName(dr) + "(\"");
                    AddText(cn);
                    AddText("\"); } set { Set" + GetMethodName(dr) + "(\"");
                    AddText(cn);
                    AddText("\", value); } }\r\n");
                }
            }
        }


        private string GetDotNetType(string dt, bool nullable)
        {
            //            System.Data.SqlDbType.in
            string n = nullable ? "?" : "";
            dt = dt.ToUpper();
            if (dt == "INT") return "int" + n;
            if (dt == "NVARCHAR") return "string";
            if (dt == "NTEXT") return "string";
            if (dt == "NCHAR") return "string";
            //            if (dt == "IMAGE") return "byte[]";
            if (dt == "DATETIME") return "DateTime" + n;
            if (dt == "MONEY") return "decimal" + n;
            if (dt == "SMALLINT") return "short" + n;
            if (dt == "BIT") return "bool" + n;
            if (dt == "REAL") return "double" + n; //???????
            return "TODO";
        }

        private string GetMethodName(SqlCeDataReader dr)
        {
            string dt = (string)dr["DATA_TYPE"];
            string n = ("YES".Equals(dr["IS_NULLABLE"])) ? "Ø" : "";

            dt = dt.ToUpper();
            if (dt == "INT") return "Int32" + n;
            if (dt == "NVARCHAR") return "String" ;
            if (dt == "NTEXT") return "String" ;
            if (dt == "NCHAR") return "String" ;
  //          if (dt == "IMAGE") return "Bytes" + n;
            if (dt == "DATETIME") return "Date" + n;
            if (dt == "MONEY") return "Decimal" + n;
            if (dt == "SMALLINT") return "Int16" + n;
            if (dt == "BIGINT") return "Int64" + n;
            if (dt == "BIT") return "Bool" + n;
//            if (dt == "REAL") return "Double" + n;
            return "TODO";
        }

        private void AddText(string text)
        {
            _sb.Append(text);
        }
    }
}
